<?php

namespace app\service;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class ExcelService
{
    // 将 Excel 日期转换为 PHP 日期,fixme 还是有时间差，需要优化，最好是excel格式规范化
    function _excelToPhpDate($excelTimestamp)
    {
        // Excel 日期是从1900-01-01开始的，但有一个已知的错误：1900年2月29日被视为有效日期。
        // 因此，我们使用1899-12-30作为起始日期来避免这个问题。
        $baseDate = strtotime('1899-12-30');

        // 分离天数和一天中的时间部分
        $days = floor($excelTimestamp);
        $timeFraction = fmod($excelTimestamp, 1);

        // 计算总秒数
        $totalSeconds = ($days * 86400) + ($timeFraction * 86400);

        // 创建一个新的 DateTime 对象，并设置为基准日期
        $dateTime = new \DateTime();
        $dateTime->setTimestamp(bcadd($baseDate, $totalSeconds));

        return $dateTime->format('Y-m-d H:i:s');
    }

    public function decodeExcel($inputFileName)
    {
        $inputFileName = public_path() . $inputFileName;
        if (!file_exists($inputFileName)) {
            return data_return_arr('文件不存在', 1);
        }
        try {
            $spreadsheet = IOFactory::load($inputFileName);
            $worksheet = $spreadsheet->getActiveSheet();

            // 获取第一行作为标题，并将标题映射到数据库字段
            $titles = $worksheet->rangeToArray('A1:' . $worksheet->getHighestColumn() . '1', null, true, false)[0];

            //todo 数据库字段与Excel标题的映射,请严格按照字段哈，如果有新的字段，请添加映射
            $fieldMap = $this->fieldMap();
            $dataList = [];
            $highestRow = $worksheet->getHighestRow();
            for ($row = 2; $row <= $highestRow; $row++) {
                $rowData = $worksheet->rangeToArray('A' . $row . ':' . $worksheet->getHighestColumn() . $row, null, true, false)[0];
                $data = [];

                foreach ($titles as $index => $title) {
                    if (isset($fieldMap[$title])) {
                        if ($title == '拓展日期') {
                            //日期有很多不规范的，需要处理
                            $date = $rowData[$index] ?? '';
                            if (!empty($date)) {
                                if (is_numeric($date)) {
                                    $date = $this->_excelToPhpDate($date);
                                } else {
                                    $date = date('Y-m-d H:i:s', strtotime($date));
                                }
                            }
                            //bs_log($date, '扩展riq', 'export.log');
                            $data[$fieldMap[$title]] = $date ?? '';
                        } else {
                            $data[$fieldMap[$title]] = $rowData[$index];
                        }
                    }
                }

                // 创建或更新数据库记录
                $merchant = new MerchantData($data);
                $merchant->save();

                //$dataList[] = $data;
            }

            return data_return_arr('解析成功', 0, $dataList);
        } catch (\Exception $e) {
            return data_return_arr('解析失败', 1, $e->getMessage());
        }
    }

    // 文件映射
    public function fieldMap()
    {
        return [
            '序号' => 'key',
        ];
    }

    // 导出Excel
    function exportExcel($data, $columns = [])
    {
        $savePath = public_path() . '/storage/excel/';
        $filename = date('YmdHis') . '.xlsx';
        $fieldMap = $this->fieldMap();
        $Spreadsheet = new Spreadsheet();
        $Sheet = $Spreadsheet->getActiveSheet();
        $Sheet->setTitle('数据列表');
        $url = request()->domain() . '/storage/excel/' . $filename;

        //过滤字段
        foreach ($fieldMap as $key => $value) {
            if ($value !== 'key' && !in_array($value, $columns)) {
                unset($fieldMap[$key]);
            }
        }
        // 设置表头
        $header = array_keys($fieldMap);
        $Sheet->fromArray([$header], null, 'A1');

        // 根据fieldMap重排$data并填充到表格中
        foreach ($data as $index => $row) {
            $mappedRow = [];
            foreach ($fieldMap as $key) {
                if ($key === 'key') {
                    $mappedRow[] = $index + 1;
                } else {
                    //$mappedRow[] = isset($row[$key]) ? '="' . $row[$key] . '"' : '=""';
                    $mappedRow[] = $row[$key] ?? '';
                }
            }
            // 填充数据
            $Sheet->fromArray([$mappedRow], null, 'A' . ($index + 2));
        }

        if (!empty($savePath)) {
            if (!is_dir($savePath)) {
                mkdir($savePath, 0777, true);
            }
            $writer = IOFactory::createWriter($Spreadsheet, 'Xlsx');
            $writer->save($savePath . DIRECTORY_SEPARATOR . $filename);
            $filename = $savePath . DIRECTORY_SEPARATOR . $filename;
        } else {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $filename . '"');
            header('Cache-Control: max-age=0');
            $writer = IOFactory::createWriter($Spreadsheet, 'Xlsx');
            $writer->save('php://output');
        }

        return data_return_arr('导出成功', 0, [
            'url' => $url
        ]);
    }

}
